package com.sprouting.metadata.core.service.queryImpl;

import com.sprouting.metadata.core.model.SimpleDataSource;
import com.sprouting.metadata.core.utils.StringTool;
import com.sprouting.metadata.core.interfaces.MetadataQueryInterfaces;
import com.sprouting.metadata.core.model.MetadataConfig;
import com.sprouting.metadata.core.utils.JdbcTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

/**
 * SqlServer2005 获取数据库的实现
 *
 * @author ：LX（长沙麓谷）
 * @date 创建时间： 2022/4/20 10:20
 */
public class SqlServer2005Inspector implements MetadataQueryInterfaces {


    /**
     * 获取表名和注释映射
     * @param simpleDataSource 数据源配置
     * @param metadataConfig 配置信息
     */
    @Override
    public Map<String, String> getTable(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig) {
        Map<String, String> answer = new HashMap<String, String>();
        try {
            StringBuilder sqlBuilder = new StringBuilder("select a.name tname, b.value comments ");
            sqlBuilder.append("from sys.tables a left join sys.extended_properties b on a.object_id=b.major_id    ");
            sqlBuilder.append("where b.minor_id=0 and b.name = 'MS_Description'   ");
            sqlBuilder.append("and a.schema_id in (   ");
            sqlBuilder.append("    select schema_id from sys.schemas  ");
            if (StringTool.isNotEmpty(metadataConfig.getSchemaPattern())) {
                sqlBuilder.append(" where name like ?   ");
            }
            sqlBuilder.append(")  and b.value is not null ");

            Connection connection = simpleDataSource.getConnection();
            // 预编译
            PreparedStatement preparedStatement = connection.prepareStatement(sqlBuilder.toString());

            if (StringTool.isNotEmpty(metadataConfig.getSchemaPattern())) {
                preparedStatement.setString(1, metadataConfig.getSchemaPattern());
            }
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                // 表名
                String key = rs.getString(StringTool.convertLetterByCase("tname", simpleDataSource));
                // 说明
                String value = rs.getString(StringTool.convertLetterByCase("comments", simpleDataSource));
                answer.put(key, value);
            }
            JdbcTool.closeResultSet(rs);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return answer;
    }

    /**
     * 获取表名和列名-注释映射
     * @param simpleDataSource 数据源配置
     * @param metadataConfig 配置信息
     */
    @Override
    public Map<String, Map<String, String>> getColumn(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig) {
        Map<String, Map<String, String>> answer = new HashMap<String, Map<String, String>>();
        try {
            StringBuilder sqlBuilder = new StringBuilder("select a.name tname, b.name cname, c.value as comments    ");
            sqlBuilder.append("from sys.tables a left join sys.columns b on a.object_id=b.object_id   ");
            sqlBuilder.append("left join sys.extended_properties c on a.object_id=c.major_id   ");
            sqlBuilder.append("where c.minor_id<>0 and b.column_id=c.minor_id   ");
            sqlBuilder.append("and a.schema_id in (   ");
            sqlBuilder.append("    select schema_id from sys.schemas  ");
            if (StringTool.isNotEmpty(metadataConfig.getSchemaPattern())) {
                sqlBuilder.append(" where name like ?   ");
            }
            sqlBuilder.append(")  and c.value is not null");

            Connection connection = simpleDataSource.getConnection();
            // 预编译
            PreparedStatement preparedStatement = connection.prepareStatement(sqlBuilder.toString());

            if (StringTool.isNotEmpty(metadataConfig.getSchemaPattern())) {
                preparedStatement.setString(1, metadataConfig.getSchemaPattern());
            }
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                String tname = rs.getString(StringTool.convertLetterByCase("tname", simpleDataSource));
                if (!answer.containsKey(tname)) {
                    answer.put(tname, new HashMap<>());
                }
                String cname = rs.getString(StringTool.convertLetterByCase("cname", simpleDataSource));
                String value = rs.getString(StringTool.convertLetterByCase("comments", simpleDataSource));
                answer.get(tname).put(cname, value);
            }

            JdbcTool.closeResultSet(rs);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return answer;
    }


}
